﻿using System.Collections.Generic;
using System.Linq;
using UcAsp.Net.Model;

namespace UcAsp.Net.MSSQLDAL
{
    public class CategoriesDAL : UcAsp.Net.IDAL.ICategoriesDAL
    {
        public List<Categories> Select(int parentid, int pagesize, int pageindex, out int pagecount)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                List<Categories> list = new List<Categories>();
                var query = entities.Categoriess.Where(o => o.ParentID == parentid);
                pagecount = list.Count;
                list = query.OrderBy(o => o.ID).Skip(pagesize * pageindex).Take(pagesize).ToList();
                return list;
            }
        }

        public Categories GetCategoriesByShowPath(string path)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                return entities.Categoriess.FirstOrDefault(o => o.ShowPath == path);
            }
        }

        public List<Categories> SelectAll()
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                return entities.Categoriess.ToList();
            }
        }

        public List<UcAsp.Net.Model.Categories> GetCategoriesSubject(string categories)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                string[] c = categories.Split(',');
                int[] categorieid = {0};
                if (c.Length > 1)
                {
                    categorieid=new int[c.Length];
                    for (int i = 0; i < c.Length; i++)
                    {
                        categorieid[i] = int.Parse(c[i]);
                    }
                }
                else
                { 
                    categorieid = new int[]{int.Parse(categories)};  
                }
                var query = from q in entities.Categoriess
                            where categorieid.Contains(q.ID)
                            select q;

                return query.ToList();
            }
        }

        public Categories GetCategoriesById(int id)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Categories categories = new Categories();
                categories = entities.Categoriess.FirstOrDefault(o => o.ID == id);
                return categories;
            }
        }

        public Categories Insert(Categories model)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                entities.Categoriess.Add(model);
                entities.SaveChanges();
                return model;
            }
        }

        public bool Update(Categories model)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                System.Data.Entity.Infrastructure.DbEntityEntry<Categories> entry = entities.Entry<Categories>(model);
                entry.State = System.Data.EntityState.Modified;
                entities.SaveChanges();
                return true;
            }
        }

        public bool Delete(int id)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                entities.Database.ExecuteSqlCommand("delete from ArticlesPage where ArticlesID in (select ID from Articles  where CategorieID=" + id + ");delete from Comments where ArticleID in (select ID from Articles  where CategorieID=" + id + ");delete from Articles where CategorieID=" + id + ";delete from SetFiled where CategoreiesID=" + id + "; delete from Categories where ID=" + id + "");
                return true;
            }
        }

        public List<Categories> SelectList(int parentid)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                int pagecount = 0;
                return Select(parentid, 200, 0, out pagecount);
            }
        }

        public List<Model.CategoriesTree> GetCategoriessTree(int parentid, List<Model.CategoriesTree> list, int i)
        {
            i++;
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                foreach (Model.Categories c in new CategoriesDAL().SelectList(parentid))
                {
                    CategoriesTree m = new CategoriesTree { CategorieName = c.CategorieName, Depth = i, ID = c.ID, ParentID = c.ParentID, ShowPath = c.ShowPath };
                    list.Add(m);
                    GetCategoriessTree(c.ID, list, i);
                }

                return list;
            }
        }

        public List<Model.CategoriesTree> GetCategoriessPath(int id)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                int i = 0;
                List<Model.CategoriesTree> list = new List<CategoriesTree>();
                if (id == 0)
                {
                    return list;
                }
                Categories c = new CategoriesDAL().GetCategoriesById(id);
                int parentid = c.ParentID;
                CategoriesTree m = new CategoriesTree { CategorieName = c.CategorieName, Depth = i, ID = c.ID, ParentID = c.ParentID, ShowPath = c.ShowPath };
                list.Add(m);
                while (parentid != 0)
                {
                    i++;
                    c = new CategoriesDAL().GetCategoriesById(parentid);
                    m = new CategoriesTree { CategorieName = c.CategorieName, Depth = i, ID = c.ID, ParentID = c.ParentID, ShowPath = c.ShowPath };
                    parentid = c.ParentID;
                    list.Add(m);
                }
                return list.OrderByDescending(o => o.Depth).ToList();
            }
        }

        public List<Model.Select> GetCategoriessComBox()
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                List<Model.CategoriesTree> list = GetCategoriessTree(0, new List<Model.CategoriesTree>(), 0);
                List<Model.Select> CategoryComBox = new List<Model.Select>();
                foreach (Model.CategoriesTree c in list)
                {
                    Model.Select box = new Model.Select();
                    string _temp = string.Empty;
                    box.Name = _temp + c.CategorieName;
                    box.Value = c.ID.ToString();
                    box.Parent = c.ParentID;
                    box.Depth = c.Depth;
                    box.ShowPath = c.ShowPath;
                    Categories ct = entities.Categoriess.FirstOrDefault(o => o.ParentID == c.ID);
                    if (ct != null)
                    {
                        box.IsParent = true;
                    }
                    else
                    {
                        box.IsParent = false;
                    }
                    CategoryComBox.Add(box);
                }
                return CategoryComBox;
            }
        }
    }
}